﻿////==========================================================================
////类名:DBUtility\MssqlDatabase.cs
////
////功能描述：数据库访问接口实现
////历史记录：
//// NO        日期          版本       姓名            内容
////--------------------------------------------------------------------------
//// 1         2012-12-22    V1.0      jimlly           
////==========================================================================
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using TonSinOA.Utility;
namespace TonSinOA.DBUtility
{
   

    public class MssqlDatabase : IDatabase,ITransaction
    {

         public static string defaultconnstring = GetConnectString("ConnectionString");


        private SqlConnection con = null;
        private SqlCommand cmd = null;
        private SqlTransaction trans = null;

        public static string GetConnectString(string key)
        {
            ConnectionStringSettings cs = ConfigurationManager.ConnectionStrings[key];
            if(cs!=null) return CryptoHelper.Decrypt( cs.ConnectionString);
            return string.Empty;
        }

        public MssqlDatabase()
        {
            con = new SqlConnection(defaultconnstring);
            cmd = new SqlCommand();
            cmd.Connection = con;
        }

        public MssqlDatabase(string connectionString)
        {
            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con;
        }


        public MssqlDatabase(SqlTransaction trans, string connectionString)
        {
            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con;
            trans = con.BeginTransaction();
        }
       
        ~MssqlDatabase()
        {
            
            this.Close();
        }
        /// <summary>
        /// 打开数据库连接.
        /// </summary>
        public void Open()
        {
            // 打开数据库连接
            if (con == null)
            {
                con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            }
            if (con.State == System.Data.ConnectionState.Closed)
                con.Open();

        }
        public void Close()
        {
            if (con != null)
            {
                con.Close();
                con = null;
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        /// <returns>A dataset containing the resultset generated by the command</returns>
        public static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            //if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            // Create & open a SqlConnection, and dispose of it after we are done
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Call the overload that takes a connection in place of the connection string
                return ExecuteDataSet(connection, commandType, commandText, commandParameters);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid SqlConnection</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        /// <returns>A dataset containing the resultset generated by the command</returns>
        public static DataSet ExecuteDataSet(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            // Create the DataAdapter & DataSet
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();

                // Fill the DataSet using default values for DataTable names, etc
                da.Fill(ds);

                // Detach the SqlParameters from the command object, so they can be used again
                cmd.Parameters.Clear();

                if (mustCloseConnection)
                    connection.Close();

                // Return the dataset
                return ds;
            }
        }

        /// <summary>
        /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
        /// to the provided command
        /// </summary>
        /// <param name="command">The SqlCommand to be prepared</param>
        /// <param name="connection">A valid SqlConnection, on which to execute this command</param>
        /// <param name="transaction">A valid SqlTransaction, or 'null'</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">An array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
        /// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            // If the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }

            // Associate the connection with the command
            command.Connection = connection;

            // Set the command text (stored procedure name or SQL statement)
            command.CommandText = commandText;

            // If we were provided a transaction, assign it
            if (transaction != null)
            {
                if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                command.Transaction = transaction;
            }

            // Set the command type
            command.CommandType = commandType;

            // Attach the command parameters if they are provided
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }

        /// <summary>
        /// This method is used to attach array of SqlParameters to a SqlCommand.
        /// 
        /// This method will assign a value of DbNull to any parameter with a direction of
        /// InputOutput and a value of null.  
        /// 
        /// This behavior will prevent default values from being used, but
        /// this will be the less common case than an intended pure output parameter (derived as InputOutput)
        /// where the user provided no input value.
        /// </summary>
        /// <param name="command">The command to which the parameters will be added</param>
        /// <param name="commandParameters">An array of SqlParameters to be added to command</param>
        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (SqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput ||
                            p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }
       
      

        public int ExecuteSql(string strSql)
        {
            SqlCommand cmd = CreateCommand(CommandType.Text, strSql, null);
            int result = 0;
            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                this.Close();
            }
            return result;
        }

        public object ExecuteObjSql(string strSql)
        {
            SqlCommand cmd = CreateCommand(CommandType.Text, strSql, null);
            object obj = null;
            try
            {
                obj = cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                this.Close();
            }
            return obj;
        }

        public DataTable ExecuteTableSql(string strSql)
        {
            SqlDataAdapter dap = new SqlDataAdapter();
            dap.SelectCommand = CreateCommand(strSql);

            DataTable dt = new DataTable();
            dap.Fill(dt);
            return dt;
        }

        public DataSet ExecuteDataSetSql(string strSql)
        {
            SqlDataAdapter dap = new SqlDataAdapter();
            dap.SelectCommand = CreateCommand(strSql);

            DataSet ds = new DataSet();
            dap.Fill(ds);
            return ds;
        }

        public DataTable ExecutePageSql(string strSql, int pageSize, int pageIndex, out int rows)
        {
            throw new NotImplementedException();
        }

        public void ExecuteProc(string procName, SqlParameter[] prams, out int result)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            cmd.ExecuteNonQuery();

            if (cmd.Parameters["ReturnValue"] != null)
            {
                result = (int)cmd.Parameters["ReturnValue"].Value;
            }
            else
            {
                result = 0;
            }
        }

        public void ExecuteProc(string procName, out SqlDataReader dataReader)
        {
            SqlCommand cmd = CreateCommand(procName, null);

            dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }

        public void ExecuteProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }

        public void ExecuteProc(string procName, SqlParameter[] prams, out DataSet ds)
        {
            SqlDataAdapter Adpt = new SqlDataAdapter();

            Adpt.SelectCommand = CreateCommand(procName, prams);

            ds = new DataSet("DataSet");
            Adpt.Fill(ds);
            this.Close();
        }
        public DataSet ExecuteProcDs(string procName, SqlParameter[] prams)
        {
            SqlDataAdapter Adpt = new SqlDataAdapter();

            Adpt.SelectCommand = CreateCommand(procName, prams);

            DataSet ds = new DataSet("DataSet");
            Adpt.Fill(ds);
            this.Close();
            return ds;
        }

        public void ExecuteProc(string procName, out DataSet ds)
        {
            SqlDataAdapter Adpt = new SqlDataAdapter();

            Adpt.SelectCommand = CreateCommand(procName, null);

            ds = new DataSet("DataSet");
            Adpt.Fill(ds);
            this.Close();
        }

        public void ExecuteProc(string procName, out DataTable dt)
        {
            SqlDataAdapter Adpt = new SqlDataAdapter();

            Adpt.SelectCommand = CreateCommand(procName, null);

            dt = new DataTable("DataTable");
            Adpt.Fill(dt);
            this.Close();
        }
        public DataTable ExecuteProcDt(string procName, SqlParameter[] prams)
        {
            SqlDataAdapter Adpt = new SqlDataAdapter();

            Adpt.SelectCommand = CreateCommand(procName, null);

            DataTable dt = new DataTable("DataTable");
            Adpt.Fill(dt);
            this.Close();
            return dt;
        }
        public void ExecuteProc(string procName, SqlParameter[] prams, out DataTable dt)
        {
            SqlDataAdapter Adpt = new SqlDataAdapter();

            Adpt.SelectCommand = CreateCommand(procName, prams);

            dt = new DataTable("DataTable");
            // Adpt.Fill(ds, "Table");
            Adpt.Fill(dt);
            this.Close();
        }

        public object ExecuteProcVal(string procName, SqlParameter[] prams)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            return cmd.ExecuteScalar();
        }
        public SqlCommand CreateCommand(string procName, SqlParameter[] prams)
        {
            // 确认打开连接

            Open();

            cmd.CommandText = procName;                      

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();                           
            if (trans != null)
            {
                cmd.Transaction = trans;

            }
            else
            {
                cmd.Transaction = null;
            }

            // 依次把参数传入存储过程

            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    cmd.Parameters.Add(parameter);

                }
            }

            // 加入返回参数
            cmd.Parameters.Add(
                new SqlParameter("ReturnValue", SqlDbType.Int, 4,
                ParameterDirection.ReturnValue, false, 0, 0,
                string.Empty, DataRowVersion.Default, null));

            return cmd;
        }
        public SqlCommand CreateCommand(CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {

            if (con.State != ConnectionState.Open)
                con.Open();
            cmd.Connection = con;
            cmd.CommandType = cmdType;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;
            else
                cmd.Transaction = null;



            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
            // 加入返回参数
            cmd.Parameters.Add(
                new SqlParameter("ReturnValue", SqlDbType.Int, 4,
                ParameterDirection.ReturnValue, false, 0, 0,
                string.Empty, DataRowVersion.Default, null));
            return cmd;
        }
        public SqlCommand CreateCommand(string cmdText)
        {

            if (con.State != ConnectionState.Open)
                con.Open();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;
            else
                cmd.Transaction = null;



            return cmd;
        }
        /// <summary>
        /// 传入输入参数
        /// </summary>
        /// <param name="ParamName">参数名称</param>
        /// <param name="DbType">参数类型</param></param>
        /// <param name="Size">参数大小</param>
        /// <param name="Value">参数值</param>
        /// <returns>新的 parameter 对象</returns>
        public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
        {

            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);

        }
        /// <summary>
        /// 传入输入参数
        /// </summary>
        /// <param name="ParamName">参数名称</param>
        /// <param name="DbType">参数类型</param></param>
        /// <param name="Size">参数大小</param>
        /// <param name="Value">参数值</param>
        /// <returns>新的 parameter 对象</returns>
        public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, object Value)
        {

            return MakeParam(ParamName, DbType, ParameterDirection.Input, Value);

        }

        //private SqlParameter MakeParam(string ParamName, SqlDbType DbType, ParameterDirection parameterDirection, object Value)
        //{
           
        //}

        /// <summary>
        /// 传入输入参数
        /// </summary>
        /// <param name="ParamName">参数名称</param>
        /// <param name="DbType">参数类型</param></param>
        /// <param name="Size">参数大小</param>
        /// <param name="Value">参数值</param>
        /// <returns>新的 parameter 对象</returns>
        static public SqlParameter MakeInOutParam(string ParamName, SqlDbType DbType, int Size, object Value)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.InputOutput, Value);

        }

        static public SqlParameter MakeInOutParam(string ParamName, SqlDbType DbType, object Value)
        {
            return MakeParam(ParamName, DbType, ParameterDirection.InputOutput, Value);

        }
        /// <summary>
        /// 传入返回值参数
        /// </summary>
        /// <param name="ParamName">参数名称</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <returns>新的 parameter 对象</returns>
        public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
        {

            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);

        }
        public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType)
        {

            return MakeParam(ParamName, DbType, ParameterDirection.Output, null);

        }
        /// <summary>
        /// 传入返回值参数
        /// </summary>
        /// <param name="ParamName">参数名称</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <returns>新的 parameter 对象</returns>
        public SqlParameter MakeReturnParam(string ParamName, SqlDbType DbType, int Size)
        {

            return MakeParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);
        }
        /// <summary>
        /// 生成存储过程参数
        /// </summary>
        /// <param name="ParamName">参数名称</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <param name="Direction">参数方向</param>
        /// <param name="Value">参数值</param>
        /// <returns>新的 parameter 对象</returns>
        static public SqlParameter MakeParam(string ParamName, SqlDbType DbType, ParameterDirection Direction, object Value)
        {
            try
            {
                SqlParameter param;
                 param = new SqlParameter(ParamName, DbType);

                param.Direction = Direction;
                if (!(Direction == ParameterDirection.Output && Value == null))
                    param.Value = Value;

                return param;
            }
            catch
            {

            }

            return null;
        }
        /// <summary>
        /// 生成存储过程参数
        /// </summary>
        /// <param name="ParamName">参数名称</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <param name="Direction">参数方向</param>
        /// <param name="Value">参数值</param>
        /// <returns>新的 parameter 对象</returns>
        static public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            try
            {
                SqlParameter param;

                if (Size > 0)
                    param = new SqlParameter(ParamName, DbType, Size);
                else
                    param = new SqlParameter(ParamName, DbType);

                param.Direction = Direction;
                if (!(Direction == ParameterDirection.Output && Value == null))
                    param.Value = Value;

                return param;
            }
            catch
            {

            }

            return null;
        }
        #region 事务处理
        public void BeginTrans()
        {
            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }
            if (trans == null)
            {
                trans = con.BeginTransaction();

            }
            else
            {
                throw new Exception("事务已经开始,无法在同一连接上再次启动事务！");
            }
        }

        public void CommitTrans()
        {
            if (trans == null)
            {
                throw new Exception("事务尚未开始,没有可用的事务用于提交！");
            }
            trans.Commit();
            trans = null;
        }
        //事务尚未开始,没有可用的事务用于回滚！
        public void RollbackTrans()
        {
            if (trans == null)
            {
                throw new Exception("事务尚未开始,没有可用的事务用于回滚！");
            }
            trans.Rollback();
            trans = null;
        }

        public IDatabase dataAccess
        {
            get { return this; }
        }
        #endregion
    }
}
